﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;

#nullable enable

namespace Bcmm.Chart.Common
{
    /// <summary>
    /// 临时SQLite数据库。
    /// </summary>
    public class TempSQLiteDataBase : IDisposable
    {
        /// <summary>
        /// 表格信息。
        /// </summary>
        private class TableInfo
        {
            /// <summary>
            /// 表格名称。
            /// </summary>
            public string TableName { get; set; } = null!;

            /// <summary>
            /// 表列数。
            /// </summary>
            public int ColumnCount { get; set; } = 0;

            /// <summary>
            /// 表行数。
            /// </summary>
            public int RowCount { get; set; } = 0;

            /// <summary>
            /// 表格关联的数据类型。
            /// </summary>
            public Type Type { get; set; } = null!;
        }

        /// <summary>
        /// 创建一个临时SQLite数据库实例。
        /// </summary>
        public TempSQLiteDataBase()
        {
            _sqliteConnection = CreateTempSQLiteConnection();
            _sqliteConnection.Open();
            _sqliteCommand = _sqliteConnection.CreateCommand();
        }

        /// <summary>
        /// 清理资源。
        /// </summary>
        public void Dispose()
        {
            _sqliteConnection.Close();
            _sqliteCommand.Dispose();
        }

        /// <summary>
        /// 临时数据库文件名。
        /// </summary>
        private string _tempDataSource { get; set; } = string.Empty;

        /// <summary>
        /// 数据库连接。
        /// </summary>
        private SQLiteConnection _sqliteConnection { get; set; }

        /// <summary>
        /// 数据库链接命令。
        /// </summary>
        private SQLiteCommand _sqliteCommand { get; set; }

        /// <summary>
        /// 表格信息列表。
        /// </summary>
        private List<TableInfo> _tableInfoList = new List<TableInfo>();

        /// <summary>
        /// 创建临时数据库链接。
        /// </summary>
        /// <returns></returns>
        private SQLiteConnection CreateTempSQLiteConnection()
        {
            _tempDataSource = CreateTempDataSource();

            var sqliteConnection = new SQLiteConnection();
            sqliteConnection.ConnectionString = $"Data Source = {_tempDataSource}";

            return sqliteConnection;
        }

        /// <summary>
        /// 根据一个表格，创建与数据类型对应的表格。暂时只支持Double,int,bool类型的属性。
        /// </summary>
        /// <typeparam name="TDataType">数据类型。</typeparam>
        /// <param name="tableName">需要创建表格的名字。</param>
        public void CreateTableByType<TDataType>(string tableName)
        {
            if (GetTableInfo(tableName) is not null)
            {
                throw new Exception($"表格{tableName}已经存在。");
            }

            Type? type = typeof(TDataType);

            var properties = type.GetProperties();

            // 首列为ID并作为索引。
            string propertyInfoList = "ID INT,";
            int columnCount = 1;

            foreach (var propertyInfo in properties)
            {
                propertyInfoList += propertyInfo.Name + " ";

                if (propertyInfo.PropertyType.Equals(typeof(double)))
                {
                    propertyInfoList += "DOUBLE" + ",";
                    columnCount++;
                }
                else if (propertyInfo.PropertyType.Equals(typeof(bool)))
                {
                    propertyInfoList += "BOOLEAN" + ",";
                    columnCount++;
                }
                else if (propertyInfo.PropertyType.Equals(typeof(int)))
                {
                    propertyInfoList += "INT" + ",";
                    columnCount++;
                }
            }

            _tableInfoList.Add(new TableInfo() { TableName = tableName, Type = type, ColumnCount = columnCount });

            // 移除最后的逗号。
            propertyInfoList = propertyInfoList.Remove(propertyInfoList.Length - 1);
            string commandString = $"CREATE TABLE {tableName} ({propertyInfoList});";

            ExecuteAction(commandString);
        }

        /// <summary>
        /// 添加数据至表格。
        /// </summary>
        /// <typeparam name="TDataType">表格数据类型。</typeparam>
        /// <param name="tableName">表格名称</param>
        /// <param name="data">需要添加的数据。</param>
        public void AddDataToTable<TDataType>(string tableName, TDataType data)
        {
            var tableInfo = GetTableInfo(tableName);

            if (tableInfo is null)
            {
                throw new Exception($"表格{tableName}不存在。");
            }

            if (!tableInfo.Type.Equals(typeof(TDataType)))
            {
                throw new Exception($"{typeof(TDataType).FullName}数据类型错误，表格{tableName}数据类型为{tableInfo.Type.FullName}。");
            }

            var type = typeof(TDataType);
            var proerties = type.GetProperties();

            tableInfo.RowCount++;
            string dataList = $"{tableInfo.RowCount},";

            foreach (var propertyInfo in proerties)
            {
                var propertyType = propertyInfo.PropertyType;
                if (propertyType.Equals(typeof(double))
                    || propertyType.Equals(typeof(bool))
                    || propertyType.Equals(typeof(int)))
                {
                    var value = propertyInfo.GetValue(data);
                    dataList += value?.ToString() + ",";
                }
            }

            dataList = dataList.Remove(dataList.Length - 1);

            string commandString = $"INSERT INTO {tableName} VALUES ({dataList});";

            ExecuteAction(commandString);

        }

        /// <summary>
        /// 添加一段范围的数据至表格。
        /// </summary>
        /// <typeparam name="TDataType">表格数据类型。</typeparam>
        /// <param name="tableName">表格名称。</param>
        /// <param name="dataList">需要添加表格数据列表。</param>
        public void AddDataRangeToTable<TDataType>(string tableName, List<TDataType> dataList)
        {
            if (dataList is null)
            {
                throw new ArgumentNullException(nameof(dataList));
            }

            foreach (var data in dataList)
            {
                AddDataToTable(tableName, data);
            }
        }

        /// <summary>
        /// 从表格中删除指定行数据。
        /// </summary>
        /// <param name="tableName">需要操作的表的名称。</param>
        /// <param name="rowIndex">需要删除指定行的行号。</param>
        public void DeleteDataFromTable(string tableName, int rowIndex)
        {
            var tableInfo = GetTableInfo(tableName);

            if (tableInfo is null)
            {
                throw new Exception($"表格{tableName}不存在。");
            }

            if (rowIndex < 1 || rowIndex > tableInfo.RowCount)
            {
                throw new ArgumentOutOfRangeException(nameof(rowIndex));
            }

            string command = $"DELETE FROM {tableName} WHERE ID='{rowIndex}';";

            ExecuteAction(command);
        }


        /// <summary>
        /// 根据表格名称获取表格信息。
        /// </summary>
        /// <param name="tableName">表格名称。</param>
        /// <returns></returns>
        private TableInfo? GetTableInfo(string tableName)
        {
            TableInfo? tableInfo = _tableInfoList.Find(x => x.TableName.Equals(tableName));

            return tableInfo;
        }


        private void WriteDataToTable()
        {

        }


        /// <summary>
        /// 从表格中移除指定索引处的数据。
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="index"></param>
        public void RomoveDataFromTable(string tableName, int index)
        {

        }

        /// <summary>
        /// 从表格中读取指定索引的数据。
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="index"></param>
        public void ReadDataFromTable(string tableName, int index)
        {
            if (index < 0)
            {
                throw new IndexOutOfRangeException(nameof(index));
            }
        }

        public void ReadDataFromTable(string tableName, int firstIndex, int count)
        {

        }

        /// <summary>
        /// 执行读取数据。
        /// </summary>
        /// <typeparam name="TDataType"></typeparam>
        /// <param name="readCommand"></param>
        /// <param name="func"></param>
        /// <returns></returns>
        private List<TDataType>? ExecuteRead<TDataType>(string readCommand, Func<SQLiteDataReader, List<TDataType>> func)
        {
            _sqliteCommand.CommandText = readCommand;

            SQLiteDataReader dataReader = _sqliteCommand.ExecuteReader();

            List<TDataType>? dataList = func?.Invoke(dataReader);

            dataReader.Close();

            return dataList;
        }

        /// <summary>
        /// 执行读取数据。
        /// </summary>
        /// <typeparam name="TDataType"></typeparam>
        /// <param name="readCommand"></param>
        /// <param name="func"></param>
        /// <returns></returns>
        private TDataType? ExecuteRead<TDataType>(string readCommand, Func<SQLiteDataReader, TDataType> func)
        {
            _sqliteCommand.CommandText = readCommand;

            SQLiteDataReader dataReader = _sqliteCommand.ExecuteReader();

            if (func is null)
            {
                return default(TDataType?);
            }

            TDataType? data = func.Invoke(dataReader);

            dataReader.Close();

            return data;
        }

        /// <summary>
        /// 执行一个操作命令。
        /// </summary>
        /// <param name="actionCommand">命令字符串。</param>
        private void ExecuteAction(string actionCommand)
        {
            _sqliteCommand.CommandText = actionCommand;
            _sqliteCommand.ExecuteNonQuery();
        }

        /// <summary>
        /// 创建一个数据库源文件。
        /// </summary>
        /// <returns></returns>
        private static string CreateTempDataSource()
        {
            var tempDataSource = Path.GetRandomFileName() + ".db";

            return tempDataSource;
        }


    }
}
